/*
 * Create by QuyenPVM
 * Servlet for Timesheet
 */
package vn.edu.fpt.xml.hrm.controller.timesheet;

import java.io.File;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.NamingException;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.stream.XMLStreamException;
import vn.edu.fpt.xml.hrm.pojo.Timesheet;
import vn.edu.fpt.xml.hrm.common.XMLUlti;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathExpressionException;
import javax.xml.xpath.XPathFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;
import vn.edu.fpt.xml.hrm.common.FormatUtil;
import vn.edu.fpt.xml.hrm.controller.AbstractController;
import vn.edu.fpt.xml.hrm.pojo.Project;
import vn.edu.fpt.xml.hrm.pojo.Projectlist;

/**
 *
 * @author Cimela
 */
public class TimesheetController extends AbstractController {

    int criteriaFlag;

    @Override
    protected String execute() {

        try {

            HttpServletRequest request = this.getRequest();

            final String homepage = "jsp/log01010.jsp";
            final String updatepage = "jsp/log01021.jsp";
            final String invalidpage = "";

            String button = request.getParameter("btAction");

            if (button.equals("Search")) {

                // format: dd/MM/yyyy
                String[] fromd = request.getParameter("txtFrom").split("/");
                String[] tod = request.getParameter("txtTo").split("/");
                String project = request.getParameter("cbProject");
                String status = request.getParameter("cbStatus");
                String fromdate = "";
                String todate = "";

                String webPath = getServletContext().getRealPath("/");
                String filePath = webPath + "WEB-INF/timesheet.xml";

                HttpSession session = request.getSession();
                if (session == null) {
                    session = request.getSession(true);
                }

                Boolean check = (Boolean) session.getAttribute("TSXML");
                if (check == null) {
                    Timesheet.createXMLTimeSheet(filePath);
                    Projectlist.importXML(webPath + "WEB-INF/project.xml");
                    session.setAttribute("TSXML", true);
                }

                Document doc = XMLUlti.createDOMTree(filePath);

                XPathFactory xpf = XPathFactory.newInstance();
                XPath xpath = xpf.newXPath();

                /* Begin create xPath */
                criteriaFlag = 0;
                String empid = (String) session.getAttribute("EMPLOYEEID");
                String exp = "//timesheet[employeeid=" + empid ;

                if (fromd.length==3) {
                    //get fromdate
                    Date temp = FormatUtil.generateDate(Integer.parseInt(fromd[2]),
                        Integer.parseInt(fromd[1]),
                        Integer.parseInt(fromd[0]));
                    fromdate = FormatUtil.formatDate(temp, "yyyyMMdd", Locale.ENGLISH);
                    exp += " and number(translate(date,'-',''))>=" + fromdate;
                }

                if (tod.length==3) {
                    //get todate
                    Date temp = FormatUtil.generateDate(Integer.parseInt(tod[2]),
                            Integer.parseInt(tod[1]),
                            Integer.parseInt(tod[0]));
                    todate = FormatUtil.formatDate(temp, "yyyyMMdd", Locale.ENGLISH);
                    exp += " and number(translate(date,'-',''))<=" + todate;
                }

                if (!project.equals("All")) {
                    exp += " and normalize-space(project)='" + project + "'";
                }

                if (!status.equals("All")) {
                    exp += " and normalize-space(status)='" + status + "'";
                }

                //if (criteriaFlag == 1) {
                    exp += "]";
                //}
                /* End create xPath */

                NodeList list = (NodeList) xpath.evaluate(exp, doc, XPathConstants.NODESET);
                List dailyreport = convert(list); // convert from Nodelist to list

                Timesheet.loadProject(request, webPath);

//                if (fromd.length==3) {
//                    Date temp = FormatUtil.generateDate(Integer.parseInt(fromd[2]),
//                            Integer.parseInt(fromd[1]),
//                            Integer.parseInt(fromd[0]));
//                    fromdate = FormatUtil.formatDate(temp, "yyyy-MM-dd", Locale.ENGLISH);
//                }
//
//                if (tod.length==3) {
//                    Date temp = FormatUtil.generateDate(Integer.parseInt(tod[2]),
//                            Integer.parseInt(tod[1]),
//                            Integer.parseInt(tod[0]));
//                    todate = FormatUtil.formatDate(temp, "yyyy-MM-dd", Locale.ENGLISH);
//                }
//
//                List dailyreport= searchTimesheet(Integer.parseInt((String) session.getAttribute("EMPLOYEEID")),
//                        fromdate, todate, project, status);

                request.setAttribute("TSheet", dailyreport);
                session.setAttribute("linkSearch", getLinkSearch(fromdate, todate, project, status));

                return homepage;

            } else if (button.equals("Delete")) {

                String[] choose = request.getParameterValues("chkChoose");

                String webPath = getServletContext().getRealPath("/");
                String filePath = webPath + "WEB-INF/timesheet.xml";

                String url = invalidpage;

                for (int i = 0; i < choose.length; i++) {
                    Timesheet temp = new Timesheet();
                    temp.setId(choose[i]);
                    if (!temp.deleteDB()) {
                        return url;
                    }
                }

                HttpSession session = request.getSession();

                //update XML
                Timesheet.createXMLTimeSheet(filePath);

                //get link Search
                url = (String) session.getAttribute("linkSearch");

                return url;

            } else if (button.equals("Update")) {

                String[] choose = request.getParameterValues("chkChoose");
                List<String> chooseList = new ArrayList();
                chooseList.addAll(Arrays.asList(choose));

                String url = invalidpage;

                if (choose != null) {

                    String webPath = getServletContext().getRealPath("/");
                    String filePath = webPath + "WEB-INF/timesheet.xml";

                    List list = new ArrayList();

                    /**************************SAX**********************************/
                    SAXParserFactory spf = SAXParserFactory.newInstance();
                    SAXParser sax = spf.newSAXParser();
                    SAXTimesheetHandler saxObj = new SAXTimesheetHandler(chooseList);
                    sax.parse(new File(filePath), saxObj);
                    list = saxObj.getTs();

                    Timesheet.loadProject(request, webPath);
                    request.setAttribute("listUpdate", list);

                    url = updatepage;
                }

                return url;
            }

        } catch (NamingException ex) {
            Logger.getLogger(TimesheetController.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(TimesheetController.class.getName()).log(Level.SEVERE, null, ex);
        } catch (XMLStreamException ex) {
            Logger.getLogger(TimesheetController.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ParserConfigurationException ex) {
            Logger.getLogger(TimesheetController.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ParseException ex) {
            ex.printStackTrace();
        } catch (XPathExpressionException ex) {
            ex.printStackTrace();
        } catch (SAXException ex) {
            ex.printStackTrace();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    private String insertCriteria(String criteria) {
        switch (criteriaFlag) {
            case 0:
                criteriaFlag = 1;
                return " and " + criteria;
            case 1:
                return " and " + criteria;
            default:
                return "";
        }
    }

    private List convert(NodeList list) throws ParseException {

        String id;
        String date;
        String project;
        String des;
        String stt;
        Double duration;
        Timesheet ts;
        List temp = new ArrayList();

        for (int i = 0; i < list.getLength(); i++) {

            if (list.item(i).getNodeName().equals("timesheet")) {

                id = list.item(i).getAttributes().getNamedItem("id").getNodeValue();
                NodeList children = list.item(i).getChildNodes();

                for (int j = 0; j < children.getLength(); j++) {

                    Node tsnode = children.item(j);
                    //get date
                    if (tsnode.getNodeName().equals("date")) {
                        //date
                        String[] datetmp = children.item(j).getTextContent().split("-");

                        date = datetmp[2] + "/" + datetmp[1] + "/" + datetmp[0];

                        Node sibling = tsnode.getNextSibling();

                        //description
                        while (!sibling.getNodeName().equals("description")) {
                            sibling = sibling.getNextSibling();
                        }
                        des = sibling.getTextContent();

                        //project
                        while (!sibling.getNodeName().equals("project")) {
                            sibling = sibling.getNextSibling();
                        }
                        project = sibling.getTextContent();

                        //duration
                        while (!sibling.getNodeName().equals("duration")) {
                            sibling = sibling.getNextSibling();
                        }
                        duration = Double.parseDouble(sibling.getTextContent());

                        //status
                        while (!sibling.getNodeName().equals("status")) {
                            sibling = sibling.getNextSibling();
                        }
                        stt = sibling.getTextContent();

                        ts = new Timesheet(id, date, des, project, duration, stt);
                        temp.add(ts);
                        break;
                    }
                } // read timesheet
            }
        } // read dailyreport
        return temp;
    }

    private String getLinkSearch(String fromdate, String todate, String project, String stt) {
        String url = "TimesheetController?btAction=Search";

        if (fromdate != null) {
            url += "&txtFrom=" + fromdate;
        }

        if (todate != null) {
            url += "&txtTo=" + todate;
        }

        if (project != null) {
            url += "&cbProject=" + project;
        }

        if (stt != null) {
            url += "&cbStatus=" + stt;
        }

        return url;
    }

    private List searchTimesheet(int empid, String fromdate, String todate, String project, String stt) throws NamingException, SQLException{

        List temp = new ArrayList();

        Context ctx = new InitialContext();
        Context envCtx = (Context) ctx.lookup("java:comp/env");
        DataSource ds = (DataSource) envCtx.lookup("DBCon");
        Connection con = ds.getConnection();

        String sql = "SELECT dr.DailyReportID, dr.Date, dr.Description, p.ProjectName, dr.Duration, dr.Status "
                + "FROM DailyReport dr INNER JOIN Project p ON dr.ProjectID = p.ProjectID "
                + "WHERE dr.EmployeeID = ? ";

        if (!fromdate.equals("")) {
            sql += "AND dr.Date >= ? ";
        }

        if (!todate.equals("")) {
            sql += "AND dr.Date <= ? ";
        }

        if (!project.equals("All")) {
            sql += "AND p.ProjectName = ? ";
        }

        if (!stt.equals("All")) {
            sql += "AND dr.Status = ? ";
        }

        PreparedStatement ps = con.prepareStatement(sql);
        
        int count =1;
        ps.setInt(count++, empid);

        if (!fromdate.equals("")) {
            ps.setString(count++, fromdate);
        }

        if (!todate.equals("")) {
            ps.setString(count++, todate);
        }

        if (!project.equals("All")) {
            ps.setString(count++, project);
        }

        if (!stt.equals("All")) {
            ps.setString(count++, stt);
        }

        ResultSet result =  ps.executeQuery();
        
        while (result.next()){
            String tsid = result.getString("DailyreportID");
            String tsdate = FormatUtil.formatDate(FormatUtil.dateToDate(result.getDate("Date")), "dd/MM/yyyy", Locale.ENGLISH);
            String tsdes = result.getString("Description");
            String tsproj =  result.getString("ProjectName");
            Double tsduration = result.getDouble("Duration");
            String tsStt = result.getString("Status");
            temp.add(new Timesheet(tsid, tsdate, tsdes, tsproj, tsduration, tsStt));
        }

        ps.close();
        con.close();

        return temp;
    }

}
